/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.impl;

import dao.AgendouDAOException;
import dao.Conexao;
import dao.HorarioDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;
import model.Horarios;

/**
 *
 * @author MauroH
 */
public class HorarioDAOImpl implements HorarioDAO {

    private Connection conn;

    public HorarioDAOImpl() throws AgendouDAOException {
        try {
            conn = Conexao.getConnection();
        } catch (Exception e) {
            throw new AgendouDAOException("Erro: \n" + e.getMessage());
        }
    }

    @Override
    public void atualizar(Horarios horario) throws AgendouDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        if (horario == null) {
            throw new AgendouDAOException("Nenhum cliente informado.");
        }
        try {
            String sql1 = "SELECT idhorarios FROM agendou.horarios WHERE data =?, horario=? AND dia_semana=?";
            ps = conn1.prepareStatement(sql1);
            ps.setDate(1, horario.getData());
            ps.setTime(2, horario.getHorario());
            ps.setString(3, horario.getDiaSemana());            
            String sql = "UPDATE agendou.horarios SET data=?, horario=?, dia_semana=?, "
                    + " WHERE idhorarios=?";
            conn1 = conn;
            ps = conn1.prepareStatement(sql);
            ps.setDate(1, horario.getData());
            ps.setTime(2, horario.getHorario());
            ps.setString(3, horario.getDiaSemana());
            ps.setString(4, sql1);            
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao atualizar o usuário. \n" + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }

    }

    @Override
    public void excluir(Horarios horario) throws AgendouDAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Statement stmt = null;
        Connection conn1 = null;
        if (horario == null) {
            throw new AgendouDAOException("Nenhum usuário informado.");
        }
        try {
            conn1 = conn;
            String sql1 = "SELECT h.* FROM agendou.horarios AS h, agendou.agenda AS a WHERE h.idhorarios != a.fk_horarios";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql1);
            int rowCont = rs.getRow();
            if (rowCont > 0) {
                String sql = "DELETE FROM agendou.usuarios WHERE data=? horario=?";
                ps = conn1.prepareStatement(sql);
                ps.setDate(1, horario.getData());
                ps.setTime(2, horario.getHorario());
                ps.executeUpdate();
            }

        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao excluir o horário. \n" + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }
    }

    @Override
    public void salvar(Horarios horario) throws AgendouDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        if (horario == null) {
            throw new AgendouDAOException("Nenhum horario informado.");
        }
        else if(horario.getData() == null || horario.getHorario() == null || horario.getDiaSemana() == null){
            System.out.println("<script type='text/javascript'>"                    
                    + "alert('Favor informar todos os dados!');"
                    + "</script>");
            throw new AgendouDAOException("Favor informar informar todos os dados.");
        }
        try {
            String sql = "INSERT INTO agendou.horarios (data, horario, dia_semana)"
                    + " values(?,?,?)";
            conn1 = conn;
            ps = conn1.prepareStatement(sql);
            ps.setDate(1, horario.getData());
            ps.setTime(2, horario.getHorario());
            ps.setString(3, horario.getDiaSemana());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao cadastrar horário. \n" + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }

    }

    @Override
    public List<Horarios> todosHorarios() throws AgendouDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM agendou.horarios ";
            conn1 = conn;
            ps = conn1.prepareStatement(sql);
            rs = ps.executeQuery();
            List<Horarios> horarios = new ArrayList<Horarios>();
            while (rs.next()) {
                Horarios horario = new Horarios(rs.getInt("idhorarios"), rs.getDate("data"), rs.getTime("horario"),
                        rs.getString("diaSemana"));
                horarios.add(horario);
            }
            return horarios;
        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao procurar horários. \n" + e);
        } finally {
            Conexao.closeConnection(conn1, ps, rs);
        }
    }
    
    public int getHorarioID(Horarios horario) throws AgendouDAOException{
        PreparedStatement ps = null;
        Connection conn1 = null;
        ResultSet rs = null;
        try {
            String sql1 = "SELECT idhorarios FROM agendou.horarios WHERE data =?, horario=? AND dia_semana=?";
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql1);
            ps.setDate(1, horario.getData());
            ps.setTime(2, horario.getHorario());
            ps.setString(3, horario.getDiaSemana());            
            rs = ps.executeQuery();
            
            return rs.getInt("idhorarios");
        } catch (SQLException e){
            throw new AgendouDAOException("Erro ao procurar horário " + e);
        } finally {
            Conexao.closeConnection(conn1, ps, rs);
        }
    }
    
    @Override
    public Horarios getHorario(Horarios horario) throws AgendouDAOException{
        PreparedStatement ps = null;
        Connection conn1 = null;
        ResultSet rs = null;
        
        try {
            String sql1 = "SELECT * FROM agendou.horarios WHERE data =? AND horario=?";
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql1);
            ps.setDate(1, horario.getData());
            ps.setTime(2, horario.getHorario());
            
            rs = ps.executeQuery();
            
            Horarios resultado;
            if( !rs.next()) {
                resultado = new Horarios();
            } else {
                resultado = new Horarios(rs.getInt("idhorarios"), rs.getDate("data"), rs.getTime("horario"),rs.getString("dia_semana"));
            }
            return resultado;
            
        } catch (SQLException e){
            throw new AgendouDAOException("Erro ao procurar horário " + e);
        } finally {
            Conexao.closeConnection(conn1, ps, rs);
        }
    }
    
    @Override
    public Horarios getHorario(int idhorario) throws AgendouDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        ResultSet rs = null;
        Horarios usuario = new Horarios();
        if (idhorario == 0) {
            throw new AgendouDAOException("Nenhum ID de horário informado.");
        }
        try {
            String sql = "SELECT * FROM agendou.horarios WHERE idhorarios=?";
            conn1 = conn;
            ps = conn1.prepareStatement(sql);
            ps.setInt(1, idhorario);
            rs = ps.executeQuery();
            Horarios resultado;
            if( !rs.next()) {
                resultado = new Horarios();
            } else {
                resultado = new Horarios(rs.getInt("idhorarios"), rs.getDate("data"), rs.getTime("horario"),rs.getString("dia_semana"));
            }
            return resultado;
        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao procurar cliente. \n" + e);
        } finally {
            Conexao.closeConnection(conn1, ps, rs);
        }
    }
}
